﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace library
{
    public partial class borrow : Form
    {
        public MySqlConnection conn = new DBclass().getConn();
        public borrow()
        {
            InitializeComponent();
        }

        private void button19_Click(object sender, EventArgs e)
        {
            conn.Open();
            string classes = comboBox1.Text;
            MySqlCommand cmd=new MySqlCommand();
            if(textBox1.Text!="")
            {
                if (classes == "书名")
                {
                    string bookname = textBox1.Text;
                    string sql = "select * from book1_info where bname like @name";
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    cmd.Parameters.Add("@name", MySqlDbType.VarChar, 100).Value = "%" + bookname + "%";
                }
                else if (classes == "作者")
                {
                    string author = textBox1.Text;
                    string sql = "select * from book1_info where author like @author";
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    cmd.Parameters.Add("@author", MySqlDbType.VarChar, 50).Value = "%" + author + "%";
                }
                else if (classes == "ISBN")
                {
                    string ISBN = textBox1.Text;
                    string sql = "select * from book1_info where ISBN like @isbn";
                    cmd.CommandText = sql;
                    cmd.Connection = conn;
                    cmd.Parameters.Add("@isbn", MySqlDbType.VarChar, 17).Value = "%" + ISBN + "%";
                }
                MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                DataTable dataTable = dataSet.Tables[0];
                int count = dataTable.Rows.Count;
                if(count==0)
                {
                    MessageBox.Show("未能检索到书籍，请检查您的输入");
                }
                string[] nums = new string[count];
                for (int i = 0; i < count; i++)
                {
                    string sql1 = "select count(*) nums from book_info where ISBN in (select ISBN from book1_info where bname=@name) and borrowed_whether='否' and booked_whether='否' and on_whether='是' and borrow_whether='是' and lose_whether='否'";
                    MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
                    cmd1.Parameters.Add("@name", MySqlDbType.VarChar, 100).Value = dataTable.Rows[i]["bname"].ToString();
                    MySqlDataAdapter adapter1 = new MySqlDataAdapter(cmd1);
                    DataSet dataSet1 = new DataSet();
                    adapter1.Fill(dataSet1);
                    DataTable dataTable1 = dataSet1.Tables[0];
                    nums[i] = dataTable1.Rows[0]["nums"].ToString();
                }


                dataTable.Columns.Add("nums");
                for (int i = 0; i < count; i++)
                {

                    dataTable.Rows[i]["nums"] = nums[i];
                }

                dataGridView5.AutoGenerateColumns = false;
                dataGridView5.DataSource = dataTable;
                this.dataGridView5.Columns["ISBN"].DataPropertyName = dataTable.Columns["ISBN"].ToString();
                this.dataGridView5.Columns["书名"].DataPropertyName = dataTable.Columns["bname"].ToString();
                this.dataGridView5.Columns["作者"].DataPropertyName = dataTable.Columns["author"].ToString();
                this.dataGridView5.Columns["出版社"].DataPropertyName = dataTable.Columns["press"].ToString();
                this.dataGridView5.Columns["出版时间"].DataPropertyName = dataTable.Columns["pub_time"].ToString();
                this.dataGridView5.Columns["可借"].DataPropertyName = dataTable.Columns["nums"].ToString();               
            }
            conn.Close();

        }

        private void dataGridView5_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                DataGridViewColumn column = dataGridView5.Columns[e.ColumnIndex];
                if (column is DataGridViewButtonColumn)
                {

                    conn.Open();
                    string sql = "select count(*) counts from borrow_info where uID=@uID and return_whether=0";
                    MySqlCommand cmd = new MySqlCommand(sql, conn);
                    cmd.Parameters.Add("@uID", MySqlDbType.Int32, 10).Value = User.uID;
                    MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                    DataSet dataSet = new DataSet();
                    adapter.Fill(dataSet);
                    DataTable dataTable = dataSet.Tables[0];
                    int num = Convert.ToInt32(dataTable.Rows[0]["counts"]);

                    string sql2 = "select * from borrow_rule where identity=@identity";
                    MySqlCommand cmd2 = new MySqlCommand(sql2, conn);
                    cmd2.Parameters.Add("@identity", MySqlDbType.VarChar, 10).Value = User.identity;
                    MySqlDataAdapter adapter2 = new MySqlDataAdapter(cmd2);
                    DataSet dataSet2 = new DataSet();
                    adapter2.Fill(dataSet2);
                    DataTable dataTable2 = dataSet2.Tables[0];
                    int maxnum = (int)dataTable2.Rows[0]["maxnum"];

                    int yearflag = 0;
                    int to_now = (int)dataTable2.Rows[0]["to_now"];
                    DateTime pubtime = (DateTime)dataGridView5.Rows[e.RowIndex].Cells[4].Value;
                    DateTime now = DateTime.Now;
                    if(now.Year-pubtime.Year>to_now)
                    {
                        yearflag = 1;
                    }

                    string sql3 = "select * from borrow_info where uID=@uid and return_whether=0";
                    MySqlCommand cmd3 = new MySqlCommand(sql3, conn);
                    cmd3.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                    MySqlDataAdapter adapter3 = new MySqlDataAdapter(cmd3);
                    DataSet dataSet3 = new DataSet();
                    adapter3.Fill(dataSet3);
                    DataTable dataTable3 = dataSet3.Tables[0];
                    int timeflag = 0;
                    for (int i = 0; i < dataTable3.Rows.Count; i++)
                    {
                        DateTime currentTime = new System.DateTime();
                        currentTime = System.DateTime.Now;
                        DateTime dateTime = (DateTime)dataTable3.Rows[i]["returntime"];
                        if (dateTime < currentTime)
                        {
                            timeflag = 1;
                            break;
                        }
                    }

                    string sql4 = "select * from fine_info where uID=@uid and pay_whether='否'";
                    MySqlCommand cmd4 = new MySqlCommand(sql4, conn);
                    cmd4.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                    MySqlDataReader reader = cmd4.ExecuteReader();
                    int fineflag = 0;
                    if (reader.Read())
                    {
                        fineflag = 1;
                    }

                    
                    conn.Close();

                    if (!(num < maxnum))
                    {
                        MessageBox.Show("您的借书限额已满，请先还书");
                    }
                    else if (timeflag == 1)
                    {
                        MessageBox.Show("您有超期未归还的图书，请先归还");
                    }
                    else if (fineflag == 1)
                    {
                        MessageBox.Show("您有未缴纳的罚款，请先缴纳罚款");
                    }
                    else if(yearflag==1)
                    {
                        MessageBox.Show("您要借的书的出版年限大于规定年限，无法借阅");
                    }
                    else
                    {
                        if (e.ColumnIndex == 6)
                        {
                            if (Convert.ToInt32(dataGridView5.Rows[e.RowIndex].Cells[5].Value) == 0)
                            {
                                MessageBox.Show("当前图书馆藏为零，可以先预约");
                            }
                            else
                            {
                                string isbn = dataGridView5.Rows[e.RowIndex].Cells[0].Value.ToString();
                                string bname = dataGridView5.Rows[e.RowIndex].Cells[1].Value.ToString();
                                borrow_do borrow_Do = new borrow_do(isbn, bname);
                                borrow_Do.Show();
                                this.Close();
                            }
                            
                        }
                        if(e.ColumnIndex==7)
                        {
                            conn.Open();
                            string isbn = dataGridView5.Rows[e.RowIndex].Cells[0].Value.ToString();
                            string sql12 = "select * from booking_info where uID=@uid and ISBN=@isbn and booking_status='已预约'";
                            MySqlCommand cmd12 = new MySqlCommand(sql12, conn);
                            cmd12.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                            cmd12.Parameters.Add("@isbn", MySqlDbType.VarChar, 17).Value = isbn;
                            MySqlDataReader reader12 = cmd12.ExecuteReader();
                            int bookingflag = 0;
                            if(reader12.Read())
                            {
                                bookingflag = 1;
                            }
                            reader12.Close();

                            string sql13 = "select * from borrow_info where uID=@uid and return_whether=0 and bID in (select bID from book_info where ISBN=@isbn)";
                            MySqlCommand cmd13 = new MySqlCommand(sql13, conn);
                            cmd13.Parameters.Add("@uid", MySqlDbType.Int32, 10).Value = User.uID;
                            cmd13.Parameters.Add("@isbn", MySqlDbType.VarChar, 17).Value = isbn;
                            MySqlDataReader reader13 = cmd13.ExecuteReader();
                            int borrowedflag = 0;
                            if(reader13.Read())
                            {
                                borrowedflag = 1;
                            }
                            reader13.Close();

                            if (Convert.ToInt32(dataGridView5.Rows[e.RowIndex].Cells[5].Value) > 0)
                            {
                                MessageBox.Show("馆藏还有，请先借阅");
                            }
                            else if(bookingflag==1)
                            {
                                MessageBox.Show("您已经预约过这本书了，请耐心等待");
                            }
                            else if(borrowedflag==1)
                            {
                                MessageBox.Show("您的在借图书中有这本书，无法预约");
                            }
                            else
                            {
                                
                                
                                string sql10 = "update book_info set booked_whether='是' where ISBN=@isbn";
                                MySqlCommand cmd10 = new MySqlCommand(sql10, conn);
                                cmd10.Parameters.Add("@isbn", MySqlDbType.VarChar, 17).Value = isbn;
                                int i = cmd10.ExecuteNonQuery();
                                if (i > 0)
                                {
                                    string sql11 = "insert into booking_info(ISBN,uID,booking_time,booking_status) values(@bid,@uid,@bookingtime,'已预约')";
                                    MySqlCommand cmd11 = new MySqlCommand(sql11, conn);
                                    cmd11.Parameters.Add("@bid", MySqlDbType.VarChar, 17).Value = isbn;
                                    cmd11.Parameters.Add("@uid", MySqlDbType.VarChar, 6).Value = User.uID;
                                    DateTime currentTime = new DateTime();
                                    currentTime = System.DateTime.Now;
                                    cmd11.Parameters.Add("@bookingtime", MySqlDbType.DateTime).Value = currentTime;
                                    int j = cmd11.ExecuteNonQuery();
                                    if(j>0)
                                    {
                                        MessageBox.Show("预约成功");
                                        
                                    }

                                }
                            }
                            
                        }
                    }
                    conn.Close();




                }
            }
        }

        private void borrow_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
        }
    }
}
